Global Internet Business Solutions ~ GIBS

nopCommerce SQL Query for Top 10 Gross Sales Days

Webmaster — Tue, 04 Feb 2025 15:26:00 GMT

Declare @current_utc_offset int IF ServerProperty('EngineEdition') >= 5 Begin set @current_utc_offset = (select convert(int, left(current_utc_offset,3)) AS current_utc_offset from sys.time_zone_info Where Name = 'US Eastern Standard Time') End PRINT @current_utc_offset SELECT TOP 10 SUM([OrderTotal]) as totalSales, Count([Id]) AS Orders, dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, convert(date, [CreatedOnUtc])), 0)) FROM [dbo].[Order] GROUP BY dateadd(hour, @current_utc_offset, dateadd(hour, datediff(hour, 0, convert(date, [CreatedOnUtc])), 0)) ORDER BY SUM([OrderTotal]) desc

nopCommerce SQL Query for Top 10 Sales Days

Webmaster — Tue, 04 Feb 2025 15:26:00 GMT

nopCommerce - Deleting Guest Accounts

Webmaster — Sun, 12 Jan 2025 10:01:00 GMT

Deleting guest account from Customer table: SQL Query DELETE Customer FROM Customer LEFT OUTER JOIN [Order] ON Customer.Id = [Order].CustomerId WHERE ([Order].Id IS NULL) AND (Customer.Email IS NULL) delete guest account - nopCommerce

How To Clear All Containers And Themes From Pages And Modules In DNN

Tue, 20 Feb 2024 20:06:00 GMT

Clear all page level skins in a Portal update {databaseOwner}{objectQualifier}Tabs set skinsrc=null where SkinSrc is not null and PortalID=0 Clear all page level containers in a Portal update {databaseOwner}{objectQualifier}tabs set containersrc=null where containersrc is not null and PortalID=0 Clear all module level containers in a Portal update {databaseOwner}{objectQualifier}TabModules set ContainerSrc = null where TabModuleID in (select TabModuleId from {databaseOwner}{objectQualifier}TabModules tm join {databaseOwner}{objectQualifier}Modules m on (tm.ModuleID = m.ModuleID) where ContainerSrc is not null and m.PortalId=0)

SQL Function to Lookup DNN Profile Values

Mon, 01 Feb 2021 20:20:00 GMT

CREATE FUNCTION GIBS_GetProfileElement ( @userID as int, @portalID as int, @ProfilePropertyName as nvarchar(100) ) RETURNS nvarchar(4000) AS BEGIN -- If input is invalid, return null. IF @ProfilePropertyName IS NULL OR LEN(@ProfilePropertyName) = 0 OR @userID IS NULL OR @userID < 1 RETURN NULL DECLARE @PropertyValue AS NVARCHAR(400) SET @PropertyValue = ( SELECT UserProfile.PropertyValue FROM (Users INNER JOIN UserProfile ON Users.UserID = UserProfile.UserID) INNER JOIN ProfilePropertyDefinition ON UserProfile.PropertyDefinitionID = ProfilePropertyDefinition.PropertyDefinitionID WHERE (((Users.UserID)=@userID) AND ((ProfilePropertyDefinition.PropertyName)=@ProfilePropertyName) AND ProfilePropertyDefinition.PortalID = @portalID) ) RETURN @PropertyValue END

Disable DNN Popup Windows

Wed, 23 Sep 2020 10:19:00 GMT

UPDATE PortalSettings SET SettingValue = 'False' WHERE SettingName = 'EnablePopUps'

Remove any leading or following spaces as well as any carriage returns

Wed, 05 Dec 2018 13:05:00 GMT

UPDATE YourTable SET YourColumn = RTRIM(LTRIM(REPLACE(REPLACE(YourColumn, CHAR(10), ''), CHAR(13), '')))

Update NBrightBuy Image Path when upgrading from V2

Tue, 20 Dec 2016 11:08:00 GMT

-- Used this great tool (https://github.com/FabioParigi/NBrightBuyMigrate ) but ran into a problem with images -- Create a SP to verify the old string you're looking to replace CREATE PROCEDURE dbo.GIBS_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname AS BEGIN TRY DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE ' SELECT @sqlCommand = @sqlCommand + 'cast([' + COLUMN_NAME + '] as nvarchar(max)) LIKE ''' + @stringToFind + ''' OR ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar','xml') SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3) EXEC (@sqlCommand) PRINT @sqlCommand END TRY BEGIN CATCH PRINT 'There was an error. Check to make sure object exists.' PRINT error_message() END CATCH GO -- Run the SP to search for the string you're looking to replace GIBS_FindStringInTable '%productimages%', 'dbo', 'NBrightBuy' GO -- Update the XMLData column with your new string UPDATE NBrightBuy SET XMLData = CAST(REPLACE(CAST(XMLData AS NVARCHAR(MAX)), 'productimages','NBStore\images') AS XML) GO select * from NBrightBuy

Order By Domain Name from Email Address

Sat, 23 Apr 2016 09:09:00 GMT

SELECT UserID, Username, FirstName, LastName, IsSuperUser, AffiliateId, Email, DisplayName, UpdatePassword, LastIPAddress, IsDeleted, CreatedByUserID, CreatedOnDate, LastModifiedByUserID, LastModifiedOnDate, PasswordResetToken, PasswordResetExpiration, LowerEmail FROM Users ORDER BY RIGHT(Email, LEN(Email) - CHARINDEX('@', Email))

DNN Region Lookup

Tue, 16 Feb 2016 11:20:00 GMT

(SELECT [Value] FROM Lists where EntryID = (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Region' AND PortalID = @PortalID))) AS [State]

SQL DNN Get User Profile with Region Lookup

Mon, 04 Jan 2016 11:13:00 GMT

Declare @PortalId int; Set @PortalId = 0; SELECT 'OwnerFirstName' = U.FirstName, 'OwnerLastName' = U.LastName, 'OwnerEmail' = U.Email , (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Telephone' AND PortalID = @PortalID)) AS OwnerTelephone , (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Cell' AND PortalID = @PortalID)) AS OwnerCell , (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Street' AND PortalID = @PortalID)) AS OwnerAddress , (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'City' AND PortalID = @PortalID)) AS OwnerCity , CASE WHEN ISNUMERIC((SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Region' AND PortalID = @PortalID))) = 1 THEN (SELECT [Value] FROM Lists where EntryID = (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Region' AND PortalID = @PortalID)) ) ELSE (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Region' AND PortalID = @PortalID)) END AS OwnerState -- (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'Region' AND PortalID = @PortalID)) AS OwnerState , (SELECT PropertyValue FROM UserProfile WHERE UserId = U.UserId AND PropertyDefinitionID = (SELECT PropertyDefinitionID FROM ProfilePropertyDefinition WHERE PropertyName = 'PostalCode' AND PortalID = @PortalID)) AS OwnerZip FROM Users U WHERE U.UserID = @UserID

Pivot Query for Ventrian Property Agent

Mon, 15 Dec 2014 13:22:00 GMT

I had a need for getting a property details for a SSR report and came up with the following store procedure . . . I thought it might be useful to someone. /****** Object: StoredProcedure [dbo].[GIBS_Rentals_Get_PropertyDetails] Script Date: 12/15/2014 06:52:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GIBS_Rentals_Get_PropertyDetails] ( @PropertyID int ) AS BEGIN SET NOCOUNT ON; DECLARE @cols AS VARCHAR(MAX), @query AS VARCHAR(MAX), @PropertyIDVarchar AS VARCHAR(10) Set @PropertyIDVarchar = Convert(VARCHAR(10),@PropertyID) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name) FROM Ventrian_PropertyAgent_CustomField FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)') ,1,1,'') --print @cols set @query = 'SELECT PropertyID,' + @cols + ' from ( SELECT PropertyID , cast(Ventrian_PropertyAgent_PropertyValue.CustomValue as Varchar(50)) AS CustomValue, Ventrian_PropertyAgent_CustomField.Name AS [Name] FROM Ventrian_PropertyAgent_PropertyValue INNER JOIN Ventrian_PropertyAgent_CustomField ON Ventrian_PropertyAgent_PropertyValue.CustomFieldID = Ventrian_PropertyAgent_CustomField.CustomFieldID WHERE Ventrian_PropertyAgent_PropertyValue.PropertyID = ' + @PropertyIDVarchar + ' ) x pivot ( min(CustomValue) for Name in (' + @cols + ') ) p ' --print @query execute(@query) END

Replace Null or Empty Value with String

Tue, 16 Sep 2014 10:28:00 GMT

Select ISNULL(NULLIF(GIBS_FBLineItems.ReportType, ''), '--Undefined--') as ReportType from GIBS_FBLineItems

COALESCE (Transact-SQL)

Mon, 12 May 2014 18:11:00 GMT

COALESCE (Transact-SQL)

Alter Table - Setting a Column Default Value

Wed, 05 Dec 2012 09:36:00 GMT

ALTER TABLE MyTableName ADD CONSTRAINT DF_MyTableName DEFAULT GETDATE() FOR MyColumnName

Updating DNN Module Skin Container via SQL

Wed, 21 Dec 2011 11:24:00 GMT

DNN module skin container settings are stored in the TabModules table. Use the following SQL statement to reset all containers: update TabModules set ContainerSrc = NULL To update spcific modules you could execute something like: update TabModules set ContainerSrc = '[G]Containers/sL001_DarkRed/No Container.ascx' where ContainerSrc = '[L]Containers/DarkRed/No Container.ascx' Host containers will start with "[G]" and site containers will start with "[L]"

Formatting a Short Date in SQL using Convert Function

Fri, 12 Aug 2011 15:26:00 GMT

To format a short date like MM/DD/YY try the following: select CONVERT(VARCHAR(8), GETDATE(), 1) as MyShortDate For a complete guide to SQL Server Date Formats visit Sql Server Helper.

Quick Table Backup - SELECT INTO

Wed, 27 Apr 2011 10:01:00 GMT

Syntax: SELECT field1 [ , field2 ] , ... [ ] INTO newtable FROM source field1 Is a parameter that specifies the list of the fields that are to be retrieved. newtable Is the name of the table the data is to be sent to. source Is the name of the table the data is to be retrieved from. The SELECT INTO statement is used to create a make-table query. It is especially useful for making backup copies of tables and reports, or for archiving records. Example: SELECT * INTO Customers_Backup FROM Customers;

DNN User Login report SQL script for DNN Reports Module

Sun, 10 Apr 2011 17:27:00 GMT

Here's a great sql script to use for the DotNetNuke Reports Module: Select -- AU.Username, U.FirstName, U.LastName, AM.loweredEmail as Email, switchoffset(CAST(AM.LastLoginDate as datetimeoffset),'-04:00') as LastLogin from aspnet_Membership as AM with (nolock) join aspnet_Users as AU with (nolock) on AM.UserID = AU.UserID join Users as U with (nolock) on U.UserName = AU.Username where AM.ApplicationID = (select ApplicationID from dbo.aspnet_Applications where ApplicationName = 'DotNetNuke') order by LastLoginDate desc